package com.example.preparedstatement.crud;

import com.example.util.JDBCUtils;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.time.Duration;
import java.time.Instant;

// 批量操作测试
public class InsertTest {

    // 方式一：使用Statement做多次插入操作
    @Test
    public void testStatementInsert() {
        Instant start = Instant.now();
        Connection connection = null;
        Statement statement = null;
        try {
            // 获取连接
            connection = JDBCUtils.getConnection();
            // 创建Statement
            statement = connection.createStatement();
            for (int i = 0; i < 20000; i++) {
                // 准备SQL
                String sql = "INSERT INTO goods(name) VALUES ('name_" + i + "')";
                // 执行查询
                statement.execute(sql);
            }
        } catch (Exception exception) {
            exception.printStackTrace();
        } finally {
            // 释放资源
            JDBCUtils.closeResource(connection, statement);
        }
        System.out.println("insert with statement 20000 times, costs:" + Duration.between(start, Instant.now())
                .toMillis() + " ms"); // 174222 ms
    }

    // 方式二：使用PreparedStatement做多次插入操作
    @Test
    public void testPreparedStatementInsert() {
        Instant start = Instant.now();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            // 获取连接
            connection = JDBCUtils.getConnection();
            // 准备SQL
            String sql = "INSERT INTO goods(name) VALUES(?)";
            // 创建PreparedStatement
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < 20000; i++) {
                // 设置参数
                preparedStatement.setString(1, "name_" + i);
                // 执行查询
                preparedStatement.execute();
            }
        } catch (Exception exception) {
            exception.printStackTrace();
        } finally {
            // 释放资源
            JDBCUtils.closeResource(connection, preparedStatement);
        }
        System.out.println("insert with preparedStatement 20000 times, costs:" + Duration.between(start, Instant.now())
                .toMillis() + " ms");// 168135 ms
    }

    // 方式三：使用PreparedStatement做批量插入操作，addBatch() executeBatch() clearBatch()
    // mysql服务器默认关闭批量操作，需要在jdbc连接串中增加参数rewriteBatchedStatements=true
    @Test
    public void testPreparedStatementBatchInsert() {
        Instant start = Instant.now();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            // 获取连接
            connection = JDBCUtils.getConnection();
            // 准备SQL
            String sql = "INSERT INTO goods(name) VALUES(?)";
            // 创建PreparedStatement
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 1; i <= 20000; i++) {
                // 设置参数
                preparedStatement.setString(1, "name_" + i);
                // 加入批量
                preparedStatement.addBatch();

                if (i % 500 == 0) {
                    // 执行批量
                    preparedStatement.executeBatch();
                    // 清除批量
                    preparedStatement.clearBatch();
                }
            }
        } catch (Exception exception) {
            exception.printStackTrace();
        } finally {
            // 释放资源
            JDBCUtils.closeResource(connection, preparedStatement);
        }
        System.out.println("insert with preparedStatement batch 20000 times, costs:" + Duration.between(start, Instant.now())
                .toMillis() + " ms");// 2653 ms
    }

    // 方式四：使用PreparedStatement做批量插入操作，addBatch() executeBatch() clearBatch()
    // 再加上禁止自动提交的操作
    @Test
    public void testPreparedStatementBatchAndDisableAutoCommitInsert() {
        Instant start = Instant.now();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            // 获取连接
            connection = JDBCUtils.getConnection();
            // 关闭自动提交
            connection.setAutoCommit(false);

            // 准备SQL
            String sql = "INSERT INTO goods(name) VALUES(?)";
            // 创建PreparedStatement
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 1; i <= 20000; i++) {
                // 设置参数
                preparedStatement.setString(1, "name_" + i);
                // 加入批量
                preparedStatement.addBatch();

                if (i % 500 == 0) {
                    // 执行批量
                    preparedStatement.executeBatch();
                    // 清除批量
                    preparedStatement.clearBatch();
                }
            }
            // 提交操作
            connection.commit();
        } catch (Exception exception) {
            exception.printStackTrace();
        } finally {
            // 释放资源
            JDBCUtils.closeResource(connection, preparedStatement);
        }
        System.out.println("insert with preparedStatement batch and disable auto commit 20000 times, costs:"
                + Duration.between(start, Instant.now()).toMillis() + " ms");// 2215 ms
    }

}
